# RUN03_Finance_Indicator_Calculate_s_Stocks_Weekly_s_SQLITE3.py
# Create by GF 2025-08-12 12:55

import sqlite3
import sys
import traceback
# ..................................................
sys.path.append("./GF_PY3_CLASS_Python_3_Text")
sys.path.append("./GF_PY3_CLASS_Finance")
# ..................................................
import pandas      # Pandas 2.0.3
import sqlalchemy  # SQLAlchemy 2.0.36
# ..................................................
from GF_PY3_CLASS.Python_3_Text_Progress_Bar import Python_3_Text_Progress_Bar
# ..................................................
from GF_PY3_CLASS_Finance_Indicator_SMA_s_SQLITE3 import Finance_Indicator_SMA_s_SQLITE3
from GF_PY3_CLASS_Finance_Indicator_EMA_s_SQLITE3 import Finance_Indicator_EMA_s_SQLITE3
from GF_PY3_CLASS_Finance_Indicator_MACD_s_SQLITE3 import Finance_Indicator_MACD_s_SQLITE3
from GF_PY3_CLASS_Finance_Indicator_KDJ_s_SQLITE3 import Finance_Indicator_KDJ_s_SQLITE3
from GF_PY3_CLASS_Finance_Entanglement_Theory_s_SQLITE3 import Finance_Entanglement_Theory_s_SQLITE3
from GF_PY3_CLASS_Finance_Data_Conv_Daily_to_Weekly_s_SQLITE3 import Finance_Data_Conv_Daily_to_Weekly_s_SQLITE3

# ##################################################

SQLITE3_PATH                              = r"./GF_SQLITE3_FINANCE.db"
DB_TABLE_WEEKLY_DATA_AGGREGATED           = r"stocks_en_us_weekly_data_aggregated"
DB_TABLE_WEEKLY_DATA_AGGREGATED_INDICATOR = r"stocks_en_us_weekly_data_aggregated_indicator"
DB_TABLE_WEEKLY_DATA_DAY_BY_DAY_UPDATE    = r"stocks_en_us_weekly_data_day_by_day_update"
STOCKS_ADJUSTED                           = r"不复权"
SQL_STATMENT_STOCKS_WEEKLY_ALL            = f"SELECT * FROM {DB_TABLE_WEEKLY_DATA_DAY_BY_DAY_UPDATE};"

# ##################################################

def DIY_Finance_DataFrame_Preparing_for_Weekly(DataFrame):

    df = DataFrame.copy()

    # 创建 "映射表 - 列名"
    Mapping_Table_for_Column_Name = {"ts_code": "code", "pct_chg": "chg_pct", "vol": "volume"}

    # 使用 "映射表 - 列名"
    df = df.rename(columns = Mapping_Table_for_Column_Name)

    df["time0"  ] = pandas.to_datetime(df["time"], format = "mixed", errors = "coerce")  # errors = "coerce" 将无效日期转为 NaT
    df["year"   ] = df["time0"  ].dt.year
    # ..............................................
    df["row_num"] = df.groupby(["code", "year", "week_num"])["time0"].rank(method = "first", ascending = True)
    df["row_num"] = df["row_num"].astype("int64")
    # ..............................................
    df["open"   ] = df["open"   ].astype("float64")
    df["high"   ] = df["high"   ].astype("float64")
    df["low"    ] = df["low"    ].astype("float64")
    df["close"  ] = df["close"  ].astype("float64")
    df["change" ] = df["change" ].astype("float64")
    df["volume" ] = df["volume" ].astype("float64")

    # 找出每年每周 (Year + Week Number) 最大行号 (最大行号 == 最后一行)
    df_max_row_num = df.groupby(["code", "year", "week_num"], as_index = False)["row_num"].max()
    df_max_row_num = df_max_row_num.rename(columns = {"row_num": "max_row_num"})
    df = pandas.merge(left = df, right = df_max_row_num, how = "left", on = ["code", "year", "week_num"])

    # 筛选每年每周 (Year + Week Number) 最后一行
    df = df[df["row_num"] == df["max_row_num"]]
    df = df.drop("max_row_num", axis = 1)

    # 重置行号 (按 Code 分组, 以 Time 大小排名作为行号) 以便连续计算
    df["row_num"] = df.groupby("code")["time0"].rank(method = "first", ascending = True)
    df["row_num"] = df["row_num"].astype("int64")

    # 重新排序 (按 Code 升序 + ROW_NUM 升序) 确保从第 1 行开始计算
    df = df.sort_values(["code", "row_num"], ascending = [True, True])
    df = df.reset_index(drop = True)

    return df

def DIY_Finance_DataFrame_Row_by_Row_Calculate_Weekly_Based_on_SQLITE3(DataFrame, to_Weekly_Aggregated:bool):

    df = DIY_Finance_DataFrame_Preparing_for_Weekly(DataFrame)

    TextProgressBar = Python_3_Text_Progress_Bar()

    # 创建 SQLite 3 数据库连接
    SQLITE3_CONNECT = sqlite3.connect(SQLITE3_PATH, check_same_thread = False, timeout = 30)  # check_same_thread = False 允许在不同的线程中使用同一个连接。

    if (to_Weekly_Aggregated == True):
        Obj101 = Finance_Indicator_SMA_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_WEEKLY_DATA_AGGREGATED_INDICATOR)
        Obj102 = Finance_Indicator_SMA_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_WEEKLY_DATA_AGGREGATED_INDICATOR)
        Obj201 = Finance_Indicator_EMA_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_WEEKLY_DATA_AGGREGATED_INDICATOR)
        Obj202 = Finance_Indicator_EMA_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_WEEKLY_DATA_AGGREGATED_INDICATOR)
        Obj300 = Finance_Indicator_MACD_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_WEEKLY_DATA_AGGREGATED_INDICATOR)
        Obj400 = Finance_Indicator_KDJ_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_WEEKLY_DATA_AGGREGATED_INDICATOR)
        Obj500 = Finance_Entanglement_Theory_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_WEEKLY_DATA_AGGREGATED_INDICATOR)
        Obj600 = Finance_Data_Conv_Daily_to_Weekly_s_SQLITE3(SQLITE3_CONNECT = SQLITE3_CONNECT, DB_TABLE = DB_TABLE_WEEKLY_DATA_AGGREGATED)

    Total = df["id"].count()
    Count = 1
    for Idx, Row in df.iterrows():
        ID       = Row["id"]
        Code     = Row["code"]
        ROW_NUM  = Row["row_num"]
        Time     = Row["time"]
        Open     = Row["open"]
        High     = Row["high"]
        Low      = Row["low"]
        Close    = Row["close"]
        Change   = Row["change"]
        Volume   = Row["volume"]
        # ..........................................
        sys.stdout.write(f"""\r[DEBUG] PROCESSING: {TextProgressBar.Double_Line_Arrow(Count = Count, Total = Total)}""")
        sys.stdout.flush()
        # ..........................................
        SMA5  = Obj101.UPDATE_OR_INSERT_SMA(ID = ID, ROW_NUM = ROW_NUM, Period = 5, Close = Close)
        SMA10 = Obj102.UPDATE_OR_INSERT_SMA(ID = ID, ROW_NUM = ROW_NUM, Period = 10, Close = Close)
        # ..........................................
        EMA12 = Obj201.UPDATE_OR_INSERT_EMA(ID = ID, ROW_NUM = ROW_NUM, Period = 12, Close = Close)
        EMA26 = Obj202.UPDATE_OR_INSERT_EMA(ID = ID, ROW_NUM = ROW_NUM, Period = 26, Close = Close)
        # ..........................................
        MACD_DIF   = Obj300.UPDATE_OR_INSERT_MACD_DIF(ID = ID, EMA12 = EMA12["value"], EMA26 = EMA26["value"])
        MACD_DEA   = Obj300.UPDATE_OR_INSERT_MACD_DEA(ID = ID, ROW_NUM = ROW_NUM, MACD_DIF = MACD_DIF["value"])
        MACD_STICK = Obj300.UPDATE_OR_INSERT_MACD_STICK(ID = ID, MACD_DIF = MACD_DIF["value"], MACD_DEA = MACD_DEA["value"])
        # ..........................................
        KDJ_K = Obj400.UPDATE_OR_INSERT_KDJ_K(ID = ID, ROW_NUM = ROW_NUM, RSV_Prd = 9, K_Prd = 3, High = High, Low = Low, Close = Close)
        KDJ_D = Obj400.UPDATE_OR_INSERT_KDJ_D(ID = ID, ROW_NUM = ROW_NUM, RSV_Prd = 9, D_Prd = 3, K_Val = KDJ_K["value"])
        KDJ_J = Obj400.UPDATE_OR_INSERT_KDJ_J(ID = ID, K_Val = KDJ_K["value"], D_Val = KDJ_D["value"])
        # ..........................................
        ETG_TRS     = Obj500.UPDATE_OR_INSERT_ETG_Top_Reversal_Shape(ID = ID, ROW_NUM = ROW_NUM, Input_UpperEdge = High, Input_LowerEdge = Low)
        ETG_BRS     = Obj500.UPDATE_OR_INSERT_ETG_Bottom_Reversal_Shape(ID = ID, ROW_NUM = ROW_NUM, Input_UpperEdge = High, Input_LowerEdge = Low)
        ETG_T_Group = Obj500.UPDATE_OR_INSERT_ETG_Top_Reversal_Shape_s_Group_Top(ID = ID, ROW_NUM = ROW_NUM, Input_UpperEdge = High, Input_LowerEdge = Low)
        ETG_B_Group = Obj500.UPDATE_OR_INSERT_ETG_Bottom_Reversal_Shape_s_Group_Bottom(ID = ID, ROW_NUM = ROW_NUM, Input_UpperEdge = High, Input_LowerEdge = Low)
        # ..........................................
        if (to_Weekly_Aggregated == True):
            # SQLITE3 TEXT 值
            TEXT_Value_01 = Obj600.UPDATE_OR_INSERT_TEXT_Field_Value_by_ID(ID = ID, Field = "code",   TEXT_Value = Code)
            TEXT_Value_02 = Obj600.UPDATE_OR_INSERT_TEXT_Field_Value_by_ID(ID = ID, Field = "time",   TEXT_Value = Time)
            # SQLITE3 REAL 值
            REAL_Value_01 = Obj600.UPDATE_OR_INSERT_REAL_Field_Value_by_ID(ID = ID, Field = "open",   REAL_Value = Open)
            REAL_Value_02 = Obj600.UPDATE_OR_INSERT_REAL_Field_Value_by_ID(ID = ID, Field = "high",   REAL_Value = High)
            REAL_Value_03 = Obj600.UPDATE_OR_INSERT_REAL_Field_Value_by_ID(ID = ID, Field = "low",    REAL_Value = Low)
            REAL_Value_04 = Obj600.UPDATE_OR_INSERT_REAL_Field_Value_by_ID(ID = ID, Field = "close",  REAL_Value = Close)
            REAL_Value_05 = Obj600.UPDATE_OR_INSERT_REAL_Field_Value_by_ID(ID = ID, Field = "change", REAL_Value = Change)
            REAL_Value_06 = Obj600.UPDATE_OR_INSERT_REAL_Field_Value_by_ID(ID = ID, Field = "volume", REAL_Value = Volume)
        # ..........................................
        Count = Count + 1

    sys.stdout.write(f"""\n""")

    # 提交 SQLite 3 数据库事务 / 关闭 SQLite 3 数据库连接
    SQLITE3_CONNECT.commit()
    SQLITE3_CONNECT.close()

# ##################################################

try:

    print(f"""[DEBUG] Finance Indicator Calculate s Stocks Weekly s SQLITE3 20250812 版""")
    print(f"""==================================================""")
    print(f"""[DEBUG] 输入 <任意数字> 并按 <Enter> 开始任务""")
    Inputed = input()

    print(f"""[DEBUG] [1] 金融指标计算 - 全量计算 (单线程)""")
    print(f"""[DEBUG] [2] 金融指标计算 - 全量计算 (多线程)""")
    print(f"""[DEBUG] [3] 金融指标计算 - 增量计算 (单线程)""")
    print(f"""==================================================""")
    print(f"""[DEBUG] 输入 <数字> 选择 <任务> 开始执行""")
    print(f"""[DEBUG] 输入 <数字>:""")
    Inputed = input()

    if (Inputed == '1'):

        # SQLite 3 in SQLalchemy URI be Similar to: "sqlite:///C:\\EXAMPLE.db"
        engine = sqlalchemy.create_engine(f"sqlite:///{SQLITE3_PATH}")
        df = pandas.read_sql_query(SQL_STATMENT_STOCKS_WEEKLY_ALL, con = engine, dtype = str)
        df = df.rename(columns = {"ts_code": "code", "pct_chg": "chg_pct", "vol": "volume"})

        DIY_Finance_DataFrame_Row_by_Row_Calculate_Weekly_Based_on_SQLITE3(DataFrame = df, to_Weekly_Aggregated = True)

    if (Inputed == '2'):

        # SQLite 3 in SQLalchemy URI be Similar to: "sqlite:///C:\\EXAMPLE.db"
        engine = sqlalchemy.create_engine(f"sqlite:///{SQLITE3_PATH}")
        df = pandas.read_sql_query(SQL_STATMENT_STOCKS_WEEKLY_ALL, con = engine, dtype = str)
        df = df.rename(columns = {"ts_code": "code", "pct_chg": "chg_pct", "vol": "volume"})

    print(f"""[DEBUG] 数据库: SQLITE3_DATASET.db 已更新""")
    print(f"""==================================================""")
    print(f"""[DEBUG] 输入 <任意数字> 并按 <Enter> 退出程序""")
    Inputed = input()

except Exception as e:

    print(f"""[DEBUG] 发生异常""")
    print(f"""==================================================""")
    traceback.print_exc()  # 打印异常信息到控制台, 而不是引发异常后退出
    print(f"""==================================================""")
    print(f"""[DEBUG] 输入 <任意数字> 并按 <Enter> 退出程序""")
    Inputed = input()

# Signed by GF.
